/*
 * Copyright (c) 2008, University of Aarhus.
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions
 * are met:
 * - Redistributions of source code must retain the above copyright notice,
 *   this list of conditions and the following disclaimer.
 * - Redistributions in binary form must reproduce the above copyright
 *   notice, this list of conditions and the following disclaimer in the
 *   documentation and/or other materials provided with the distribution.
 * - Neither the name of the Univeristy of Aarhus nor the names
 *   of its contributors may be used to endorse or promote products derived
 *   from this software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED
 * TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA,
 * OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY
 * OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
 * USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * @author: Morten Tranberg Hansen <mth@cs.au.dk>
 * @date:   May 8 2008	
 * 
 */

package dk.sensobyg.basestation;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.LinkedList;

import org.apache.log4j.Logger;

@Deprecated
public class DatabaseCache implements Cache {

	private static Logger log = Logger.getLogger("dk.sensobyg.basestation.DatabaseCache");
	private final static int CACHE_SIZE_PER_MOTE = 5;
	private final static int MAX_RETRIES = 3; 
	
    private Connection connection;
    private String host, user, pass, database, table;
    
	public DatabaseCache(String dbHost, String dbUser, String dbPass, String dbDatabase, String dbTable) throws SQLException {
		host = dbHost;
		user = dbUser;
		pass = dbPass;
		database = dbDatabase;
		table = dbTable;
		connect();
	}
	
	public void connect() throws SQLException {
		try {
			// Register the JDBC driver for MySQL.
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			log.fatal("Could not find database driver.", e);
			System.exit(1);
		}
		// Create connection to MySQL server
		connection = DriverManager.getConnection("jdbc:mysql://"+host+"/"+database, user, pass);
	}
	
	public void disconnect() throws SQLException{
		if(connection!=null)
			connection.close();
	}
	
	synchronized public void addData(DataItem item) throws CacheException {
            
		try {
			if(connection.isClosed()) {
				connect();
			}
			
			Statement stmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

			stmt.executeUpdate("LOCK TABLES "+table+" WRITE;");
			
			// Insert new record.
			stmt.executeUpdate("INSERT INTO "+table+" (moteid, timestamp, datatype, data) VALUES ('" + 
					item.getMoteid() + "','" +
					item.getTimestamp() + "','" + 
					item.getDataType() + "','" + 
					item.getData() + "')");
			
			// Delete old records.
			ResultSet result = stmt.executeQuery("SELECT COUNT(*) FROM "+table+" WHERE moteid="+item.getMoteid());
			result.next();
			int cacheSize = result.getInt("COUNT(*)");
			if(cacheSize>CACHE_SIZE_PER_MOTE) {
				stmt.executeUpdate("DELETE FROM "+table+" WHERE moteid="+item.getMoteid()+" ORDER BY timestamp LIMIT "+(cacheSize-CACHE_SIZE_PER_MOTE)+";");
			}
			
			stmt.executeUpdate("UNLOCK TABLES;");
			
		} catch(SQLException e) {
           	String message = "Could not add data to database."; 
   			log.error(message, e);
   			throw new CacheException(message, e);			
		}
	}
	
    public List<DataItem> getData(String conditions) throws CacheException {
    	List<DataItem> dataItems = new LinkedList<DataItem>();
    	try {
    		if(connection.isClosed()) {
				connect();
			}
    		Statement stmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    		String whereClause = "";
    		if(!conditions.equals(""))
    			whereClause = " WHERE "+conditions;
    		ResultSet result = stmt.executeQuery("SELECT * FROM "+table+whereClause+" ORDER BY timestamp DESC;");
    		while(result.next()) {
    			dataItems.add(new DataItem(result.getInt("moteid"), result.getLong("timestamp"), result.getShort("datatype"), result.getLong("data")));
    		}
    	} catch(SQLException e) {
    		String message = "Could not get data from database."; 
    		log.error(message, e);
    		throw new CacheException(message, e);
    	}
    	return dataItems;
    }
    
    public void clear() throws CacheException {
    	try {
    		if(connection.isClosed()) {
				connect();
			}
    		Statement stmt = connection.createStatement();
    		stmt.executeQuery("TRUNCATE TABLE "+table+";");
    	} catch(SQLException e) {
    		String message = "Could not clear database."; 
    		log.error(message, e);
    		throw new CacheException(message, e);
    	}
    }
}
